<html>
<body>
	<table border="1">
		<tr>
			<td valign="top">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="index.html" />
			</td>
			<td><%@ page language="java" import="java.sql.*,java.util.*,java.text.*"%>
			<%@ page import="javax.sql.rowset.CachedRowSet,com.sun.rowset.CachedRowSetImpl;" %>
			
			<p>Produce the grade report of student X</p>
				<%
					Connection conn = null;
					try {
						// Load Oracle Driver class file
						DriverManager
								.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

						// Make a connection to the MS SQL database "cse132b"
						conn = DriverManager
								.getConnection(
										"jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=cse132b",
										"cse132b", "cse132b");
	
						String action = request.getParameter("action");
						// Check if an insertion is requested
						CachedRowSet cachedQuery = new CachedRowSetImpl();
						CachedRowSet cachedAvgGPA = new CachedRowSetImpl();
						CachedRowSet cachedCumGPA = new CachedRowSetImpl();
						if (action != null && action.equals("submit")) {
							// Begin transaction
							conn.setAutoCommit(false);
							
							PreparedStatement pstmt = conn
									.prepareStatement("SELECT course_number, title, quarter, year, units, grade FROM enroll, section, class " +
							"WHERE student_id = ? AND enroll.section_id = section.section_id AND section.class_id = class.class_id " +
							"ORDER BY year desc");     //descending order??
							pstmt.setString(1, request.getParameter("student_id")); 
							ResultSet rs =pstmt.executeQuery();
							cachedQuery.populate(rs);
							
							pstmt = conn
									.prepareStatement("SELECT quarter, year, sum(number_grade*units)/sum(units) AS average FROM enroll, section, class, grade_conversion " + 
							"WHERE student_id = ? AND enroll.section_id = section.section_id AND section.class_id = class.class_id AND enroll.grade = letter_grade " +
									"AND grade_type = 'letter' GROUP BY quarter, year");
							pstmt.setString(1, request.getParameter("student_id")); 
							rs =pstmt.executeQuery();
							cachedAvgGPA.populate(rs);
							
							pstmt = conn
									.prepareStatement("SELECT sum(number_grade*units)/sum(units) AS average FROM enroll, grade_conversion " + 
							"WHERE student_id = ? AND enroll.grade = letter_grade AND grade_type = 'letter'");
							pstmt.setString(1, request.getParameter("student_id")); 
							rs =pstmt.executeQuery();
							cachedCumGPA.populate(rs);
							
							conn.setAutoCommit(true);
						}
			
						// Create the statement
						Statement statement = conn.createStatement();
						
						ResultSet rsStudent = statement.executeQuery("SELECT student_id, first_name, middle_name, last_name, ssn FROM student " +
						 "WHERE student_id IN (SELECT student_id FROM student_enrollment)");
						CachedRowSet cachedStudent = new CachedRowSetImpl();
						cachedStudent.populate(rsStudent);

				%>
				<table border="1">
					<tr>
						<form action="report1c.jsp" method="POST">
							<input type="hidden" value="submit" name="action">
							<th>
								<select name="student_id" style="width:200px">
								<%
								String studentId = request.getParameter("student_id");
								while(cachedStudent.next()){
								%>
									<option value=<%=cachedStudent.getString("student_id")%> 
									<%if(studentId!=null && cachedStudent.getString("student_id").equals(studentId)) out.println("selected = selected");%> >
									<%=cachedStudent.getString("first_name")%> 
									<%=cachedStudent.getString("middle_name")%> 
									<%=cachedStudent.getString("last_name")%> 
									ssn: <%=cachedStudent.getString("ssn")%> 
									</option>
								<% 	
								}
								%>
								</select>
							</th>
							<th><input type="submit" value="submit"></th>
						</form>
					</tr>
					<tr>
						<th>Course Number</th>
						<th>Title</th>
						<th>Quarter</th>
						<th>Year</th>
						<th>Units</th>
						<th>Grade</th>
					</tr>
					
					<%
						// Iterate over the ResultSet
						while (cachedQuery.next()) {
							
					%>
					<tr>
                        <td><%=cachedQuery.getString("course_number") %></td>
                        <td><%=cachedQuery.getString("title") %></td>
						<td><%=cachedQuery.getString("quarter") %></td>
						<td><%=cachedQuery.getString("year") %> </td>
                        <td><%=cachedQuery.getInt("units") %></td>
                        <td><%=cachedQuery.getString("grade") %></td>
                    </tr>
                
                <%
						}
                %>
                </table>
                
                
                <p>Average GPA each quarter</p>
				<table border="1">
				<tr> <th>Quarter</th><th>Average GPA</th></tr>
				
				<%while(cachedAvgGPA.next()){ 
					DecimalFormat df = new DecimalFormat("#.###");
					double avgGpa = Double.parseDouble(df.format(cachedAvgGPA.getDouble("average")));
				%>
				<tr>
					<td><%=cachedAvgGPA.getString("quarter")%> <%=cachedAvgGPA.getString("year") %></td>
					<td><%=avgGpa%></td>
				</tr>
				<%} %>
				</table>
				
				
				<%	
					if(cachedCumGPA.next()){ 
						DecimalFormat df = new DecimalFormat("#.###");
						double cumGpa = Double.parseDouble(df.format(cachedCumGPA.getDouble("average")));
						
				%>
				
				<p>Cumulative GPA: <%=cumGpa%></p>
				<%} %>
				
				<%
					} catch (SQLException e) {
						System.out.println(e.getMessage());
						//conn.rollback();
					} catch (Exception e) {
						System.out.println(e.getMessage());
						//conn.rollback();
					}
				%>
				
				
				
				
			</td>
		</tr>
	</table>
</body>
</html>